package student;

import java.sql.*;
import java.util.*;

public class StudentDao {
	public static int PAGE_SIZE = 10;

	// 增
	public int add(Student st) {
		Connection con = DBUtil.getConnection();
		int i = 0;
		String sql = "insert into s_student (sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,address,pass,idcardpath) values (?,?,?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, st.getSname());
			ps.setString(2, st.getSex());
			ps.setString(3, st.getTelephone());
			ps.setString(4, st.getQq());
			ps.setString(5, st.getWechat());
			ps.setLong(6, st.getGradeId());
			// ps.setDate(7, new java.sql.Date(st.getEnterDate().getTime()));
			ps.setDate(7, st.getEnterDate() == null ? null : new java.sql.Date(st.getEnterDate().getTime()));
			ps.setString(8, st.getFromcity());
			// ps.setDate(9, new java.sql.Date(st.getBirthday().getTime()));
			ps.setDate(9, st.getBirthday() == null ? null : new java.sql.Date(st.getBirthday().getTime()));
			ps.setString(10, st.getAddress());
			ps.setString(11, st.getPass());
			ps.setString(12, st.getIdcardpath());
			i = ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return i;
	}

	// 删
	public int del(long stuno) {
		Connection con = DBUtil.getConnection();
		int i = 0;
		String sql = "delete from s_student where stuno= " + stuno + "";
		System.out.println(sql);
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			i = ps.executeUpdate();
			System.out.println("result:" + i);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return i;
	}

	// 改
	public int update(Student st) {
		Connection con = DBUtil.getConnection();
		int i = 0;
		String sql = "update s_student set pass='" + st.getPass() + "' where stuno=" + st.getStuno() + "";
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			i = ps.executeUpdate();
			System.out.println("result:" + i);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return i;
	}

	// 改2
	public int updateNew(Student st) {
		Connection con = DBUtil.getConnection();
		int i = 0;
		String sql = "update s_studet set sname='" + st.getSname() + "',pass='" + st.getPass() + "'where stuno="
				+ st.getStuno() + "";
		PreparedStatement ps = null;
		try {
			ps = con.prepareStatement(sql);
			i = ps.executeUpdate();
			System.out.println("result:" + i);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return i;

	}

	// 修改3
	public int newUpdate(Student st) {
		Connection con = DBUtil.getConnection();// 调用get方法创建连接
		PreparedStatement ppst = null;// 创建preparedstatement来执行sql语句
		String sql = "update s_student set sname = ?,sex = ?,telephone = ?,qq = ?,"
				+ "wechat = ?,gradeid = ?,enterdate = ?,fromcity = ?,birthday = ?,address = ?,pass = ?  where stuno = ?";
		int i = 0;
		try {
			ppst = con.prepareStatement(sql);
			ppst.setString(1, st.getSname());
			ppst.setString(2, st.getSex());
			ppst.setString(3, st.getTelephone());
			ppst.setString(4, st.getQq());
			ppst.setString(5, st.getWechat());
			ppst.setLong(6, st.getGradeId());
			ppst.setDate(7, st.getEnterDate() == null ? null : new java.sql.Date(st.getEnterDate().getTime()));
			ppst.setString(8, st.getFromcity());
			ppst.setDate(9, st.getBirthday() == null ? null : new java.sql.Date(st.getBirthday().getTime()));
			ppst.setString(10, st.getAddress());
			ppst.setString(11, st.getPass());
			ppst.setLong(12, st.getStuno());
			i = ppst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(ppst);
			DBUtil.close(con);
		}
		return i;
	}

	// （查）R:分为两种：1：查单个对象；
	public Student get(long stuno) {
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Student stu = null;
		// String sql = "select * from s_student where stuno=?";
		String sql = "select s.*,g.gname from s_student s INNER JOIN s_grade g on(s.gradeid=g.id) where s.stuno=?";
		try {
			ps = con.prepareStatement(sql);
			ps.setLong(1, stuno);
			rs = ps.executeQuery();
			if (rs.next()) {
				stu = new Student();
				stu.setStuno(rs.getLong("stuno"));
				stu.setSname(rs.getString("sname"));
				stu.setAddress(rs.getString("address"));
				stu.setBirthday(rs.getDate("birthday"));
				stu.setSex(rs.getString("sex"));
				stu.setTelephone(rs.getString("telephone"));
				stu.setQq(rs.getString("qq"));
				stu.setWechat(rs.getString("wechat"));
				stu.setGradeId(rs.getLong("gradeid"));
				stu.setEnterDate(rs.getDate("enterdate"));
				stu.setFromcity(rs.getString("fromcity"));
				stu.setPass(rs.getString("pass"));
				stu.setIdcardpath(rs.getString("idcardpath"));
				stu.setGrade(rs.getString("gname"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return stu;
	}

	// 2:查全部
	public List<Student> searchAll() {
		String sql = "select * from s_student order by stuno asc";

		List<Student> datas = new ArrayList<>();
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Student stu = null;
		try {
			ps = con.prepareStatement(sql);

			rs = ps.executeQuery();
			while (rs.next()) {
				stu = new Student();
				stu.setStuno(rs.getLong("stuno"));
				stu.setSname(rs.getString("sname"));
				stu.setAddress(rs.getString("address"));
				stu.setBirthday(rs.getDate("birthday"));
				stu.setSex(rs.getString("sex"));
				stu.setTelephone(rs.getString("telephone"));
				stu.setQq(rs.getString("qq"));
				stu.setWechat(rs.getString("wechat"));
				stu.setGradeId(rs.getLong("gradeid"));
				stu.setEnterDate(rs.getDate("enterdate"));
				stu.setFromcity(rs.getString("fromcity"));
				stu.setPass(rs.getString("pass"));
				datas.add(stu);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return datas;

	}

	// 分页,count(*)
	/**
	 * 
	 * @param pageSize 页大小
	 * @param pageNo   页号
	 * @return
	 */
	public List<Student> paging(int pageSize, int pageNo) {

		// String sql = "select * from s_student order by stuno asc limit ?,? ";
		String sql = "select s.*,g.gname from s_student s INNER JOIN s_grade g on(s.gradeid=g.id) order by stuno asc limit ?,?";
		List<Student> datas = new ArrayList<>();
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Student stu = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, pageSize * (pageNo - 1));
			ps.setInt(2, pageSize);
			rs = ps.executeQuery();
			while (rs.next()) {
				stu = new Student();
				stu.setStuno(rs.getLong("stuno"));
				stu.setSname(rs.getString("sname"));
				stu.setAddress(rs.getString("address"));
				stu.setBirthday(rs.getDate("birthday"));
				stu.setSex(rs.getString("sex"));
				stu.setTelephone(rs.getString("telephone"));
				stu.setQq(rs.getString("qq"));
				stu.setWechat(rs.getString("wechat"));
				// stu.setGradeId(rs.getLong("gradeid"));
				stu.setEnterDate(rs.getDate("enterdate"));
				stu.setFromcity(rs.getString("fromcity"));
				stu.setPass(rs.getString("pass"));
				stu.setGrade(rs.getString("gname"));
				datas.add(stu);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return datas;
	}

	/**
	 * 
	 * @param pageSize 页大小
	 * @param pageNo   页号
	 * @return
	 */
	public List<Student> paging(int pageNo) {

		// String sql = "select * from s_student order by stuno asc limit ?,? ";
		String sql = "select s.*,g.gname from s_student s INNER JOIN s_grade g on(s.gradeid=g.id) order by stuno asc limit ?,?";
		List<Student> datas = new ArrayList<>();
		Connection con = DBUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		Student stu = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, PAGE_SIZE * (pageNo - 1));
			ps.setInt(2, PAGE_SIZE);
			rs = ps.executeQuery();
			while (rs.next()) {
				stu = new Student();
				stu.setStuno(rs.getLong("stuno"));
				stu.setSname(rs.getString("sname"));
				stu.setAddress(rs.getString("address"));
				stu.setBirthday(rs.getDate("birthday"));
				stu.setSex(rs.getString("sex"));
				stu.setTelephone(rs.getString("telephone"));
				stu.setQq(rs.getString("qq"));
				stu.setWechat(rs.getString("wechat"));
				// stu.setGradeId(rs.getLong("gradeid"));
				stu.setEnterDate(rs.getDate("enterdate"));
				stu.setFromcity(rs.getString("fromcity"));
				stu.setPass(rs.getString("pass"));
				stu.setGrade(rs.getString("gname"));
				datas.add(stu);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return datas;
	}

	// 用户登录
	public NewsUser login(long stuno, String p) {
		Connection con = DBUtil.getConnection();
		String sql = "select pass,sname from s_student where stuno=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		NewsUser nu = null;
		try {
			ps = con.prepareStatement(sql);
			ps.setLong(1, stuno);
			rs = ps.executeQuery();
			while (rs.next()) {
				String pass = rs.getString("pass");
				if (pass.equals(p)) {
					nu = new NewsUser();
					nu.setStuno(stuno);
					nu.setPass(p);
					nu.setSname(rs.getString("sname"));
				}

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		return nu;
	}

	// 获得页数
	public int getTotalPage() {
		Connection con = DBUtil.getConnection();
		String sql = "select count(stuno) from s_student ";
		PreparedStatement ps = null;
		ResultSet rs = null;
		int i = 0;
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				i = rs.getInt("count(stuno)");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(ps);
			DBUtil.close(con);
		}
		int page = i / PAGE_SIZE + (i % PAGE_SIZE == 0 ? 0 : 1);
		return page;
	}
}
